import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class ExcelUtil {
    public static void main(String args[]){
        //标题行
        List<String> columnNameList = new ArrayList<String>();
        columnNameList.add("名称");
        columnNameList.add("测试列1");
        columnNameList.add("测试列2");
        //数据
        Map<String,List<Map<String,String>>> monValueList = new LinkedHashMap<String, List<Map<String, String>>>();

        List<Map<String,String>> valueList = new ArrayList<Map<String, String>>();
        Map<String,String> valueMap = new HashMap<String, String>();
        valueMap.put("名称","202012");
        valueMap.put("测试列1","sheet1_01_1");
        valueMap.put("测试列2","sheet1_01_2");
        Map<String,String> valueMap1 = new HashMap<String, String>();
        valueMap1.put("名称","202012");
        valueMap1.put("测试列1","sheet1_01_1");
        valueMap1.put("测试列2","sheet1_02_2");

        valueList.add(valueMap);
        valueList.add(valueMap1);

        List<Map<String,String>> valueList1 = new ArrayList<Map<String, String>>();
        Map<String,String> valueMap11 = new HashMap<String, String>();
        valueMap11.put("名称","202101");
        valueMap11.put("测试列1","sheet2_01_1");
        valueMap11.put("测试列2","sheet2_01_2");
        Map<String,String> valueMap12 = new HashMap<String, String>();
        valueMap12.put("名称","202101");
        valueMap12.put("测试列1","sheet2_02_1");
        valueMap12.put("测试列2","sheet2_02_2");

        valueList1.add(valueMap11);
        valueList1.add(valueMap12);

        monValueList.put("202012",valueList);
        monValueList.put("202101",valueList1);

        SXSSFWorkbook wb = new SXSSFWorkbook(1000);
        for (String key:monValueList.keySet()
             ) {
            SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("详细列表"+key);
//            wb.setActiveSheet(0);
            sheet.createFreezePane(2, 1);
            CellStyle style = getStyle(wb);

            //创建标题行
            SXSSFRow headRow = (SXSSFRow) sheet.createRow(0);
            for (int i = 0; i < columnNameList.size(); i++) {
                String columnName = columnNameList.get(i);
                SXSSFCell cell = (SXSSFCell) headRow.createCell(i);
                cell.setCellStyle(style);
                sheet.setColumnWidth(i, columnName.length() * 512);
                cell.setCellValue(columnName);
            }
            for (int i = 0; i < monValueList.get(key).size(); i++) {
                Map<String, String> mapValue = monValueList.get(key).get(i);
                //创建一行
                SXSSFRow valueRow = (SXSSFRow) sheet.createRow(i + 1);
                //创建每行的列
                for (int j = 0; j < columnNameList.size(); j++) {
                    String columnName = columnNameList.get(j);
                    SXSSFCell cell = (SXSSFCell) valueRow.createCell(j);
                    cell.setCellValue(String.valueOf(mapValue.get(columnName)));
                }
            }
        }




        try {
            wb.write(new FileOutputStream("E://test.xlsx"));
        } catch (IOException e) {
            System.out.println("Excel文件生成失败");
        }
    }
    private static CellStyle getStyle(SXSSFWorkbook wb) {
        Font font = wb.createFont();
        CellStyle style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFillForegroundColor(HSSFColor.YELLOW.index);
        style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        style.setFont(font);
        //设置换行
        style.setWrapText(true);
        return style;
    }
    //public static void main(String args[]){
    //        Map<String,List<Map<String, String>>> monValueList = new LinkedHashMap<>();
    //        List<Map<String, String>> valueList = new ArrayList<>();
    //        Map<String,String> map = new HashMap<>();
    //
    //        map.put("name","ddd");
    //        map.put("age","23");
    //        map.put("sex","girl");
    //        valueList.add(map);
    //        monValueList.put("202001",valueList);
    //        monValueList.put("202002",valueList);
    //        monValueList.put("202003",valueList);
    //        monValueList.put("202004",valueList);
    //        List<Map<String,String>> testList = new ArrayList<>();
    //        Iterator it = monValueList.entrySet().iterator();
    //        while (it.hasNext()) {
    //            Map.Entry entry = (Map.Entry) it.next();
    //            List<Map<String,String>> list = (List<Map<String, String>>) entry.getValue();
    //            testList.addAll(list);
    //            System.out.println("key:" + entry.getKey() + "   value:" + entry.getValue());
    //        }
    //        System.out.println(monValueList.size());
    //        System.out.println(testList.size());
    //
    ////        String startMonth = "202009";
    ////        String endMonth = "202101";
    ////        int startY = Integer.parseInt(startMonth.substring(0,4));
    ////        int startM = Integer.parseInt(startMonth.substring(4));
    ////
    ////        int endY = Integer.parseInt(endMonth.substring(0,4));
    ////        int endM = Integer.parseInt(endMonth.substring(4));
    //////        int allM = 1;
    //////        if (startY == endY){
    //////            if (startM != endM){
    //////                allM = endM-startM;
    //////            }
    //////        }else{
    //////            allM = ((12-startM)+1)+endM;
    //////        }
    //////
    //////        System.out.println(allM);
    ////
    ////        List<String> list = new ArrayList<String>();
    ////        for (int i = startY; i <= endY; i++) {
    ////            String date = "";
    ////            if (startY == endY) {
    ////                for (int j = startM-1; j < endM; j++) {
    ////                    if (j < 9) {
    ////                        date = i + "0" + (j + 1);
    ////                    } else {
    ////                        date = i + "" + (j + 1);
    ////                    }
    ////                    list.add(date);
    ////                }
    ////
    ////            } else {
    ////                if (i == startY) {
    ////                    for (int j = startM-1; j < 12; j++) {
    ////                        if (j < 9) {
    ////                            date = i + "0" + (j + 1);
    ////                        } else {
    ////                            date = i + "" + (j + 1);
    ////                        }
    ////                        list.add(date);
    ////                    }
    ////                } else if (i == endY) {
    ////                    for (int j = 0; j < endM; j++) {
    ////                        if (j < 9) {
    ////                            date = i + "0" + (j + 1);
    ////                        } else {
    ////                            date = i + "" + (j + 1);
    ////                        }
    ////                        list.add(date);
    ////                    }
    ////                } else {
    ////                    for (int j = 0; j < 12; j++) {
    ////                        if (j < 9) {
    ////                            date = i + "0" + (j + 1);
    ////                        } else {
    ////                            date = i + "" + (j + 1);
    ////                        }
    ////                        list.add(date);
    ////                    }
    ////                }
    ////
    ////            }
    ////        }
    ////
    ////        // 所有的月份已经准备好
    ////        for(int i = 0;i < list.size();i++){
    ////            System.out.println(list.get(i));
    ////        }
    //    }
}
